|
文章目录行转列首先我们先创建一个表给表赋值行转列mysql语句这里大部分第一次学mysql 行转列地时候看到这个语句会很奇怪为啥要用MAX函数呢？ 这个不是多余地吗？列转行举例首先我们需要创建一个表列转行表赋值列转行mysql语句
行转列首先我们先创建一个表
表如下
CREATE TABLE test_hjsjy_1(
`id` int(10) NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) DEFAULT NULL,
`course` varchar(20) DEFAULT NULL,
`score` float DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=INNODB auto_increment=1 DEFAULT CHARSET=utf8;
给表赋值
INSERT INTO test_hjsjy_1(user_name,COURSE,score) VALUES
("老大","数学",56),
("老大","语文",35),
("老大","英语",87),
("老二","数学",36),
("老二","语文",78),
("老二","英语",52),
("老三","数学",72),
("老三","语文",91),
("老三","英语",59);
行转列mysql语句
SELECT user_name,
MAX(CASE course WHEN '数学' THEN score ELSE 0 END) 数学,
MAX(CASE course WHEN '语文' THEN score ELSE 0 END) 语文,
MAX(CASE course WHEN '英语' THEN score ELSE 0 END) 英语
from test_hjsjy_1
GROUP BY user_name;
效果如下
这里大部分第一次学mysql 行转列地时候看到这个语句会很奇怪为啥要用MAX函数呢？ 这个不是多余地吗？
解答： 这并不是多余地，其实我们来测试一下我不加max看是什么效果
SELECT user_name ,
(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
FROM test_hjsjy_1
GROUP BY user_name;
看一下我们查询的效果
这里我们会发现我们老大老二老三的语文和英语成绩怎么没了都是0？
我们再尝试看看把上面的查询语句中group by user_name 试着去掉
SELECT user_name ,
(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
FROM test_hjsjy_1
再看看查询结果
聪明的人的现在知道为啥需要用max函数了，其实是因为group by分组，你没有max函数 他就会查询每个分组的第一个元组，所以max函数是不能少的，这个问题也启发我们，遇到这种问题我们可以通过调试sql，来解决我们遇到的疑惑。
列转行举例首先我们需要创建一个表
表如下
CREATE TABLE `test_hjsjy_2` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) DEFAULT NULL,
`chinese_score` float DEFAULT NULL,
`math_score` float DEFAULT NULL,
`english_score` float DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
列转行表赋值
insert into test_hjsjy_2(user_name, chinese_score, math_score, english_score) values
("老大", 34, 58, 58),
("老二", 45, 87, 45),
("老三", 76, 34, 89);
列转行mysql语句
select user_name, '语文' COURSE , chinese_score as SCORE from test_hjsjy_2
union select user_name, '数学' COURSE, math_score as SCORE from test_hjsjy_2
union select user_name, '英语' COURSE, english_score as SCORE from test_hjsjy_2
order by user_name,course;
表结果如下
这样我们也就实现了列转行。
|